package com.scholarship.modules.backend.service;


import com.scholarship.common.dto.ResultListDto;
import com.scholarship.common.utils.*;
import com.scholarship.modules.admin.mapper.ARewardFeedbackMapper;
import com.scholarship.modules.admin.mapper.AUserInfoMapper;
import com.scholarship.modules.admin.service.InOutFileService;
import com.scholarship.modules.backend.dao.ListRewardTable;
import com.scholarship.modules.backend.dao.RewardTableQuery;
import com.scholarship.modules.backend.dao.RewardTableSearch;
import com.scholarship.modules.backend.entity.StudentScore;
import com.scholarship.modules.backend.mapper.RewardTableMapper;
import com.scholarship.modules.backend.mapper.TeacherAuthorityMapper;
import com.scholarship.modules.login.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Pattern;

/**
 * @author zhz
 * @date 2021/5/28 13:17
 * 备注：
 */
@Slf4j
@Service
public class RewardTableService
{
    @Autowired
    RewardTableMapper rewardTableMapper;
    @Autowired
    TeacherAuthorityMapper teacherAuthorityMapper;
    @Autowired
    private NewFileUtil newFileUtil;
    @Autowired
    UploadToYun uploadToYun;
    @Autowired
    InOutFileService inOutFileService;
    @Autowired
    ARewardFeedbackMapper aRewardFeedbackMapper;
    @Autowired
    AUserInfoMapper aUserInfoMapper;

    private static Pattern COMMON_PATTERN = Pattern.compile("^[a-z]([0-9]+)");

    //判断教师是否有操作该表id的权限
    public Boolean isTeacherAuthority(Integer tableId){
        Subject subject= SecurityUtils.getSubject();
        String userId = ((User)subject.getPrincipal()).getUserId();
        System.out.println(userId);
        //从第0为开始记权限
        tableId-=1;
        //判断教师是否有该表权限
        if((1<<tableId)<teacherAuthorityMapper.getTableAuthority(userId) && ((teacherAuthorityMapper.getTableAuthority(userId) >> tableId)&1)==1) return true;
        return false;
    }

    //根据tableId返回数据库表名
    public String getTableNameById(Integer id){
        return rewardTableMapper.getTableNameById(id);
    }

    //根据tableId返回真实表名
    public String getRewardTableNameById(Integer id){
        return rewardTableMapper.getRewardTableNameById(id);
    }

    //根据tableId列出表头信息
    public List listTitleByTableId(Integer id){
        List<String> list = new LinkedList<>();
        list.addAll(Arrays.asList("序号", "学号","姓名","班级"));
        list.addAll(rewardTableMapper.listTitleByTableId(id));
        if(isUploadFile(id)){
            list.add("附件图片");
        }
        list.add("获得分值");
        list.add("状态");
        return list;
    }

    //根据tableId返回键值对信息<表头信息（中文），属性名（英文）>
    public Map listTableAttribute(Integer id){
        LinkedHashMap<String,Object> mp = new LinkedHashMap<>();
        mp.put("学号","user_id");
        mp.put("姓名","name");
        mp.put("班级","stu_class");
        List<LinkedHashMap<String,String>> lt = rewardTableMapper.listRewardTableAttribute(id);
        for(Map x:lt){
            mp.put((String)x.get("item_name"),x.get("attribute_name"));
        }
        if(isUploadFile(id)){
            mp.put("附件图片","file_path");
        }
        mp.put("获得分值","score");
        mp.put("状态","state");
        return mp;
    }

    //根据tableId列出表的所有信息
    public List<LinkedHashMap<String,Object>> listTableByTableId(Integer id){
        List<String> list = new LinkedList<>();
        list.addAll(rewardTableMapper.listAttributeByTableId(id));
        ListRewardTable listRewardTable = new ListRewardTable(list,id,getTableNameById(id),0,getSumTableByTableId(id).intValue());
        List<LinkedHashMap<String,Object>> mp = rewardTableMapper.listRewardTableByTableId(listRewardTable);
        return mp;
    }

    /**
     * 实现分页返回tableId的所有信息
     * @param id 表id
     * @param page 页数
     * @param size 每页的大小
     * @return
     */
    public ResultListDto listRewardTableByTableId(Integer id,Integer page,Integer size){
        List<String> list = rewardTableMapper.listAttributeByTableId(id);
//      [start,pageSize)
        int left = (page - 1) * size;
        ListRewardTable listRewardTable = new ListRewardTable(list,id,getTableNameById(id),left,size);
        List<LinkedHashMap<String,Object>> listMp = rewardTableMapper.listRewardTableByTableId(listRewardTable);
        return new ResultListDto<>(getSumTableByTableId(id),listMp);
    }

    /**
     * 根据查询条件分页显示出信息
     * @param rewardTableQuery
     * @return
     */
    public ResultListDto listRewardTableByQuery(RewardTableQuery rewardTableQuery){
        Integer tableId = rewardTableQuery.getTableId();
        List<String> list = rewardTableMapper.listAttributeByTableId(tableId);
        int left = (rewardTableQuery.getPage() - 1) * rewardTableQuery.getSize();
        ListRewardTable listRewardTable = new ListRewardTable(list,tableId,getTableNameById(tableId),left,rewardTableQuery.getSize(), rewardTableQuery.getState(), rewardTableQuery.getStuClass());
        List<LinkedHashMap<String,Object>> listMp = rewardTableMapper.listRewardTableByQuery(listRewardTable);
        return new ResultListDto<>(getSumTableByTableId(tableId,rewardTableQuery.getState(), rewardTableQuery.getStuClass()),listMp);
    }
    //列出所有表id和对应的表命
    public List listRewardTable(){return rewardTableMapper.listRewardTable();}

    //给学生批改成绩
    public Integer updateTableScore(StudentScore studentScore){return rewardTableMapper.updateTableScore(studentScore);}

    //更新所有学生成绩
    @Transactional(rollbackFor=Exception.class)//事务的注解，失败就rollback
    public Integer upStuScore(){
        //获取导入年份
        Calendar calendar = Calendar.getInstance();
        String importYear = String.valueOf(calendar.get(Calendar.YEAR));
        //更新总申请状态
        aRewardFeedbackMapper.updateStatesByBarringApplicationState(importYear,3,1);
        //更新已申请奖学金学生的总成绩
        aUserInfoMapper.updateTotalScore();
        //进行三选二的判断，扣去三项中的最低分
        threeChoiceeTwo();
        //更新总排名
        aUserInfoMapper.updateTotalRank();
        return rewardTableMapper.updateStuScore();
    }

    /**
     * 生成导出模板
     * @param tableId
     * @return
     * @throws Exception
     */
    public ResponseEntity<byte[]> exportRewardTable(Integer tableId) throws Exception {

        List<String> fieldNames = new ArrayList<>();
        fieldNames.addAll(listTitleByTableId(tableId));
        //生成Excel文件

        // 创建写工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 工作表
        HSSFSheet sheet = workbook.createSheet();
        //设置字体
        HSSFFont font = workbook.createFont();//创建字体样式
        font.setFontName("宋体");//使用宋体
        font.setFontHeightInPoints((short) 10);//字体大小
//        font.setBold(true);// 加粗
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //然后创建单元格样式style
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);//将字体注入
        style.setWrapText(true);// 自动换行
        style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
//        style1.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());// 设置单元格的背景颜色
//        style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
//        style.setBorderTop((short) 1);// 边框的大小
//        style.setBorderBottom((short) 1);
//        style.setBorderLeft((short) 1);
//        style.setBorderRight((short) 1);
//        sheet.setColumnWidth(0, 20*256);
//        sheet.setColumnWidth(1, 20*256);
//        sheet.setColumnWidth(2, 20*256);

        HSSFRow row = sheet.createRow(0);
//        row.setHeight((short) 800);// 设定行的高度//5、创建row中的单元格，从0开始
        //设置备注
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(getRewardTableNameById(tableId));
        //合并单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, fieldNames.size()-1);
        sheet.addMergedRegion(region);
        //设置表头
        row = sheet.createRow(1);
        LinkedHashMap<String,Integer> cellMap = new LinkedHashMap<>();
        Map attributeMap = listTableAttribute(tableId);
        for(int i = 0;i <fieldNames.size();i++) {
//            row = sheet.createRow(i);
            cell = row.createCell(i);
            cell.setCellValue(fieldNames.get(i));
            cellMap.put((String) attributeMap.get(fieldNames.get(i)),i);
        }

        //填入表格数据，如果图片，就给图片重命名
        List<LinkedHashMap<String,Object>> listMap = listTableByTableId(tableId);
        for(int i=0;i<listMap.size();i++){
            row = sheet.createRow(2+i);
            Map<String, Object> mmm = listMap.get(i);
            cell = row.createCell(0);
            cell.setCellValue(i+1);
            for (Map.Entry<String, Object> entry : mmm.entrySet()) {
                if(cellMap.get(entry.getKey())==null)  continue;
                //如果字段是图片，就重命名
                if(entry.getKey().equals("file_path")) {
                    String tmp[] = entry.getValue().toString().split("\\.");
                    entry.setValue(mmm.get("user_id").toString()+"+"+mmm.get("name").toString()+"."+tmp[tmp.length-1]);
                }
                cell = row.createCell(cellMap.get(entry.getKey()));
                cell.setCellValue(entry.getValue().toString());
            }
        }
        //保存表格
        String excelPath = getRewardTableNameById(tableId) + ".xlsx";//FileDownloadUtil.PATH
        FileOutputStream outputStream = new FileOutputStream(excelPath);
        workbook.write(outputStream);
        outputStream.close();

        //下载文件
        ResponseEntity<byte[]> excel;
        File file = new File(excelPath);
        HttpHeaders headers = new HttpHeaders();
        headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
        // 设置下载时的文件名，中文文件名需要进行转码，否则是乱码
        headers.add("Content-Disposition",
                "attachment; filename=" + new String(excelPath.getBytes("UTF-8"),
                        "iso-8859-1"));
//        headers.add("Content-Disposition",
//                "attachment; filename=" + java.net.URLEncoder.encode("你好.xlsx", "UTF-8"));
        headers.add("Pragma", "no-cache");
        headers.add("Expires", "0");
        excel =new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED);
        //删除临时生成的Excel表
        file = new File(excelPath);
        if (file.exists() && file.isFile()) {
            file.delete();
        }
        return excel;
    }

    /**
     * 收集学生图片，打包成压缩包
     * @param tableId
     * @throws Exception
     */
    public void exportRewardPicturesZip(Integer tableId, HttpServletResponse response) throws Exception{
        List<LinkedHashMap<String,Object>> listMap = listTableByTableId(tableId);
        List<File> fileList = new LinkedList<>();
        for(int i=0;i<listMap.size();i++){
            Map<String, Object> mmm = listMap.get(i);
            for (Map.Entry<String, Object> entry : mmm.entrySet()) {
                //如果字段是图片，就重命名
                if(entry.getKey().equals("file_path")) {
                    String real_path = entry.getValue().toString();
                    String tmp[] = entry.getValue().toString().split("\\.");
                    String file_path = mmm.get("user_id").toString()+"_"+mmm.get("name").toString()+"."+tmp[tmp.length-1];
                    uploadToYun.getFile(uploadToYun.downloadFile(real_path).bytes(),file_path);
                    File file = new File(uploadToYun.getFilePath()+file_path);
                    fileList.add(file);
                }
            }
        }
        List<String> fieldNames = new ArrayList<>();
        fieldNames.addAll(listTitleByTableId(tableId));
        fileList.add(inOutFileService.createImportTemplateFile(tableId,true));
        try {
            //下载压缩包
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="
                    + URLEncoder.encode(getTableNameById(tableId)+".zip","utf-8"));
            newFileUtil.downloadZip(fileList,response.getOutputStream());
            for(File f:fileList){
                if (f.exists() && f.isFile()) {
                    f.delete();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //根据表id判断改表是否需要文件上传
    Boolean isUploadFile(Integer id){
        if(rewardTableMapper.isUploadFile(id)==1)return true;
        return false;
    }

    //驳回学生奖学金申请
    @Transactional(rollbackFor=Exception.class)//事务的注解，失败就rollback
    public void giveStudentFeedback(String userId,Integer tableId,String feedback){
        try {
            rewardTableMapper.giveStudentFeedback(userId,tableId,feedback);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //批改单个学生奖学金申请
    @Transactional(rollbackFor=Exception.class)//事务的注解，失败就rollback
    public void giveStudentScore(String userId,Integer tableId,Float score){
        try {
            rewardTableMapper.giveStudentScore(userId,tableId,score);
//            rewardTableMapper.updateStuScoreByUserId(userId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获得某个表学生提交的数量
    public Long getSumTableByTableId(Integer id){
        ListRewardTable listRewardTable = new ListRewardTable();
        listRewardTable.setTableId(id);
        listRewardTable.setTableName(getTableNameById(id));
        return rewardTableMapper.getSumTableByTableId(listRewardTable);
    }

    //获得某个表学生提交的数量
    public Long getSumTableByTableId(Integer id,String state,String stuClass){
        ListRewardTable listRewardTable = new ListRewardTable();
        listRewardTable.setTableId(id);
        listRewardTable.setTableName(getTableNameById(id));
        listRewardTable.setState(state);
        listRewardTable.setStuClass(stuClass);
        return rewardTableMapper.getSumTableByTableId(listRewardTable);
    }

    //获得某个表学生提交的数量
    public Long getSumTableByTableId(Integer id,String searchStr){
        ListRewardTable listRewardTable = new ListRewardTable();
        listRewardTable.setTableId(id);
        listRewardTable.setTableName(getTableNameById(id));
        listRewardTable.setSearchStr(searchStr);
        return rewardTableMapper.getSumTableByTableId(listRewardTable);
    }

    //根据搜索查询奖学金申请列表
    public ResultListDto listRewardTableBySearch(RewardTableSearch rewardTableSearch){
        Integer tableId = rewardTableSearch.getTableId();
        List<String> list = rewardTableMapper.listAttributeByTableId(tableId);
        int left = (rewardTableSearch.getPage() - 1) * rewardTableSearch.getSize();
        ListRewardTable listRewardTable = new ListRewardTable(list,tableId,getTableNameById(tableId),left,rewardTableSearch.getSize(),rewardTableSearch.getSearchStr());
        List<LinkedHashMap<String,Object>> listMp = rewardTableMapper.listRewardTableBySearch(listRewardTable);
        return new ResultListDto<>(getSumTableByTableId(tableId,rewardTableSearch.getSearchStr()),listMp);
    }

//    根据tableName返回id
    public Integer getTableIdByName(String tableName){
        return rewardTableMapper.getTableIdByName(tableName);
    }

    //进行三选二的判断
    public Integer threeChoiceeTwo(){
        return rewardTableMapper.threeChoiceTwo();
    }
}
